﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Coomtools.dbhelper
{
    public static class sqlhelper
    {
        /// <summary>
        /// 1.连接字符串  主要工作数据库的连接
        /// </summary>
      
        
        public static string connectionString ;

        /// <summary>
        /// 2.连接字符串 用于验证注册用户第二个数据库连接
        /// </summary>
        public static string conString2;   
        #region 2.测试数据库是否连接成功
        /// <summary>
        /// 测试数据库是否连接成功 成功返回true
        /// </summary>
        /// <param name="connstr">连接字符串</param>
        /// <returns></returns>
        public static bool databaseconnectOk(string connectionString)

        {
            using (SqlConnection sqlcon = new SqlConnection(connectionString))
            {
                try
                {
                    sqlcon.Open();
                    sqlcon.Close();
                    return true;
                }
                catch
                {
                    sqlcon.Close();
                    return false;


                }

                finally
                {
                    sqlcon.Close();
                }


            }

        }
        #endregion

        #region 3.执行对数据库执行增、删、改命令
        /// <summary>
        /// 没有参数时 执行对数据库执行增、删、改命令
        /// </summary>
        /// <param name="safeSql">增、删、改sql语句</param>
        /// <returns>执行后影响的行数</returns>
        public static int ExecuteNonQuery(string safeSql)
        {
            using (SqlConnection Connection = new SqlConnection(connectionString))
            {
                Connection.Open();
                SqlTransaction trans = Connection.BeginTransaction();
                try
                {
                    SqlCommand cmd = new SqlCommand(safeSql, Connection);
                    cmd.Transaction = trans;

                    if (Connection.State != ConnectionState.Open)
                    {
                        Connection.Open();
                    }
                    int result = cmd.ExecuteNonQuery();
                    trans.Commit();
                    return result;
                }
                catch
                {
                    trans.Rollback();
                    return 0;
                }
            }

        }


        /// <summary>  
        /// 有参数的时候 对数据库执行增、删、改命令  
        /// </summary>  
        /// <param name="sql">T-Sql语句</param>  
        /// <param name="values">参数数组</param>  
        /// <returns>受影响的记录数</returns>  
        public static int ExecuteNonQuery(string sql, SqlParameter[] values)
        {
            using (SqlConnection Connection = new SqlConnection(connectionString))
            {
                Connection.Open();
                SqlTransaction trans = Connection.BeginTransaction();
                try
                {
                    SqlCommand cmd = new SqlCommand(sql, Connection);
                    cmd.Transaction = trans;
                    cmd.Parameters.AddRange(values);
                    if (Connection.State != ConnectionState.Open)
                    {
                        Connection.Open();
                    }
                    int result = cmd.ExecuteNonQuery();
                    trans.Commit();
                    return result;
                }
                catch 
                {
                    trans.Rollback();
                    return 0;
                }
            }
        }
        #endregion

        #region 4.ExecuteScalar命令查询结果集中第一行第一列的值 
        /// <summary>  
        /// 查询结果集中第一行第一列的值  
        /// </summary>  
        /// <param name="safeSql">T-Sql语句</param>  
        /// <returns>第一行第一列的值</returns>  
        public static int ExecuteScalar(string safeSql)
        {
            using (SqlConnection Connection = new SqlConnection(connectionString))
            {
                if (Connection.State != ConnectionState.Open)
                    Connection.Open();
                SqlCommand cmd = new SqlCommand(safeSql, Connection);
                int result = Convert.ToInt32(cmd.ExecuteScalar());
                return result;
            }
        }

        /// <summary>  
        /// 查询结果集中第一行第一列的值  
        /// </summary>  
        /// <param name="sql">T-Sql语句</param>  
        /// <param name="values">参数数组</param>  
        /// <returns>第一行第一列的值</returns>  
        public static int ExecuteScalar(string sql, SqlParameter[] values)
        {
            using (SqlConnection Connection = new SqlConnection(connectionString))
            {
                if (Connection.State != ConnectionState.Open)
                    Connection.Open();
                SqlCommand cmd = new SqlCommand(sql, Connection);
                cmd.Parameters.AddRange(values);
                int result = Convert.ToInt32(cmd.ExecuteScalar());
                return result;
            }
        }

        /// <summary>
        /// 命令查询结果集中第一行第一列的值返回objec对象
        /// </summary>
        /// <param name="sqlstr">sql语句</param>
        /// <param name="pms">参数数组 可以不填</param>
        /// <returns></returns>
        public static object Select_ExecuteScalar(string sqlstr, params SqlParameter[] pms)
        {
            using (SqlConnection con = new SqlConnection(connectionString))
            {

                using (SqlCommand cmd = new SqlCommand(sqlstr, con))
                {
                    if (pms != null)
                    {
                        cmd.Parameters.AddRange(pms);
                    }
                    try
                    {
                        con.Open();
                        return cmd.ExecuteScalar();
                    }
                    catch (SqlException ex)
                    {

                        throw ex;
                    }

                }
            }
        }

        #endregion

        #region 5.ExecuteReader命令 执行查询服务端 多行多列的数据的方法：ExecuteReader 持续连接查询
        /// <summary>  
        /// 新无参数执行查询服务端 多行多列的数据的方法  
        /// </summary>  
        /// <param name="safeSql">T-Sql语句</param>  
        /// <param name="connectionString">数据库连接字符串</param>  
        /// <returns>数据读取器对象</returns>  

        public static SqlDataReader ExecuteReader(string connectionString,string safeSql )
            {
            SqlConnection Connection = new SqlConnection(connectionString);
            if (Connection.State != ConnectionState.Open)
                    Connection.Open();
                SqlCommand cmd = new SqlCommand(safeSql, Connection);
                SqlDataReader reader = cmd.ExecuteReader();
                return reader;
            }

        /// <summary>  
        /// 新有参数执行查询服务端 多行多列的数据的方法 
        /// </summary>  
        /// <param name="sql">T-Sql语句</param>  
        /// <param name="values">参数数组</param>  
        /// <param name="connectionString">数据库连接字符串</param>  
        /// <returns>数据读取器</returns>  
        public static SqlDataReader ExecuteReader(string connectionString, string sql, SqlParameter[] values)
            {
            SqlConnection Connection = new SqlConnection(connectionString);
            if (Connection.State != ConnectionState.Open)
                    Connection.Open();
                SqlCommand cmd = new SqlCommand(sql, Connection);
                cmd.Parameters.AddRange(values);
                SqlDataReader reader = cmd.ExecuteReader();
                return reader;
            }


        /// <summary>
        /// 执行查询服务端 多行多列的数据的方法不需要连接字符串参数 但是连接字符串静态变量的先生成
        /// </summary>
        /// <param name="sqlstr">sql语句</param>
        /// <param name="pms">参数数组</param>
        /// <returns></returns>
        public static SqlDataReader Select_ExecuteReader(string sqlstr, params SqlParameter[] pms)
            {
                SqlConnection con = new SqlConnection(connectionString);
                using (SqlCommand cmd = new SqlCommand(sqlstr, con))
                {
                    if (pms != null)
                    {
                        cmd.Parameters.AddRange(pms);
                    }
                    try
                    {
                        con.Open();
                        //CommandBehavior.CloseConnection  连接关闭 readr关闭
                        return cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    }
                    catch (Exception ex)
                    {
                        con.Close();
                        con.Dispose();
                        throw ex;
                    }
                }
            }

        #endregion


        #region 6.查询本地 执行返回DataTable的方法 ExecuteDataTable命令
        /// <summary>  
        /// 执行指定数据库连接对象的命令,指定存储过程参数,返回DataTable  
        /// </summary>  
        /// <param name="type">命令类型(T-Sql语句或者存储过程)</param>  
        /// <param name="safeSql">T-Sql语句或者存储过程的名称</param>  
        /// <param name="values">参数数组</param>  
        /// <returns>结果集DataTable</returns>  
        public static DataTable ExecuteDataTable(CommandType type, string safeSql, params SqlParameter[] values)
            {
                using (SqlConnection Connection = new SqlConnection(connectionString))
                {
                    if (Connection.State != ConnectionState.Open)
                        Connection.Open();
                    DataSet ds = new DataSet();
                    SqlCommand cmd = new SqlCommand(safeSql, Connection);
                    cmd.CommandType = type;
                    SqlDataAdapter da = new SqlDataAdapter(cmd);
                    da.Fill(ds);
                    return ds.Tables[0];
                }
            }

            /// <summary>  
            /// 执行指定数据库连接对象的命令,指定存储过程参数,返回DataTable  
            /// </summary>  
            /// <param name="safeSql">T-Sql语句</param>  
            /// <returns>结果集DataTable</returns>  
            public static DataTable ExecuteDataTable(string safeSql)
            {
                using (SqlConnection Connection = new SqlConnection(connectionString))
                {
                    if (Connection.State != ConnectionState.Open)
                        Connection.Open();
                    DataSet ds = new DataSet();
                    SqlCommand cmd = new SqlCommand(safeSql, Connection);
                    SqlDataAdapter da = new SqlDataAdapter(cmd);
                    try
                    {
                        da.Fill(ds);
                    }
                    catch 
                    {

                    }
                    return ds.Tables[0];
                }
            }

            /// <summary>  
            /// 执行指定数据库连接对象的命令,指定存储过程参数,返回DataTable  
            /// </summary>  
            /// <param name="sql">T-Sql语句</param>  
            /// <param name="values">参数数组</param>  
            /// <returns>结果集DataTable</returns>  
            public static DataTable ExecuteDataTable(string sql, params SqlParameter[] values)
            {
                using (SqlConnection Connection = new SqlConnection(connectionString))
                {
                    if (Connection.State != ConnectionState.Open)
                        Connection.Open();
                    DataSet ds = new DataSet();
                    SqlCommand cmd = new SqlCommand(sql, Connection);
                    cmd.CommandTimeout = 0;
                    cmd.Parameters.AddRange(values);
                    SqlDataAdapter da = new SqlDataAdapter(cmd);
                    da.Fill(ds);
                    return ds.Tables[0];
                }
            }

     /// <summary>
     /// 查询返回本地DataTable的查询
     /// </summary>
     /// <param name="sqlstr">sql语句</param>
     /// <param name="pms">参数数组</param>
     /// <returns></returns>
        public static DataTable Select_DataTable(string sqlstr, params SqlParameter[] pms)
        {
            DataTable dt = new DataTable();
            try
            {
                using (SqlDataAdapter adapter = new SqlDataAdapter(sqlstr, connectionString))
                {
                    if (pms != null)
                    {
                        adapter.SelectCommand.Parameters.AddRange(pms);
                    }
                    adapter.Fill(dt);
                }
                return dt;
            }
            catch (Exception ex)
            {

                throw ex;
            }



        }

        #endregion


        #region 7.查询结果返回本地dataset的方法   GetDataSet命令
        /// <summary>  
        /// 取出数据返回本地dataset的方法  
        /// </summary>  
        /// <param name="safeSql">sql语句</param>  
        /// <param name="tabName">DataTable别名</param>  
        /// <param name="values"></param>  
        /// <returns></returns>  
        public static DataSet GetDataSet(string safeSql, string tabName, params SqlParameter[] values)
            {
                using (SqlConnection Connection = new SqlConnection(connectionString))
                {
                    if (Connection.State != ConnectionState.Open)
                        Connection.Open();
                    DataSet ds = new DataSet();
                    SqlCommand cmd = new SqlCommand(safeSql, Connection);

                    if (values != null)
                        cmd.Parameters.AddRange(values);

                    SqlDataAdapter da = new SqlDataAdapter(cmd);
                    try
                    {
                        da.Fill(ds, tabName);
                    }
                    catch 
                    {

                    }
                    return ds;
                }
            }

        #endregion


        #region 8.批量修改数据 ExecureData 命令
        /// <summary>  
        /// 批量修改数据  
        /// </summary>  
        /// <param name="ds">修改过的DataSet</param>  
        /// <param name="strTblName">表名</param>  
        /// <returns></returns>  
        public static int ExecureData(DataSet ds, string strTblName)
            {
                try
                {
                    //创建一个数据库连接  
                    using (SqlConnection Connection = new SqlConnection(connectionString))
                    {
                        if (Connection.State != ConnectionState.Open)
                            Connection.Open();

                        //创建一个用于填充DataSet的对象  
                        SqlCommand myCommand = new SqlCommand("SELECT * FROM " + strTblName, Connection);
                        SqlDataAdapter myAdapter = new SqlDataAdapter();
                        //获取SQL语句，用于在数据库中选择记录  
                        myAdapter.SelectCommand = myCommand;

                        //自动生成单表命令，用于将对DataSet所做的更改与数据库更改相对应  
                        SqlCommandBuilder myCommandBuilder = new SqlCommandBuilder(myAdapter);

                        return myAdapter.Update(ds, strTblName);  //更新ds数据  
                    }

                }
                catch (Exception err)
                {
                    throw err;
                }
            }


        #endregion


        #region 9.ExecuteSqlListTran 事务方式批量执行sql语句全在 List<string>里的非查询语句 

        /// <summary>
        /// 事务方式批量执行sql语句全在 List<string>里的非查询语句 
        /// </summary>
        /// <param name="SQLStringList">非查询sql语句列表</param>
        /// <returns>成功返回总受影响的行数大于等于0 -1表示事务执行失败</returns>

        public static int ExecuteSqlListTran(List<string> SQLStringList)
        {
            int result;
          
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand
                {
                    Connection = conn
                };
                SqlTransaction tx = conn.BeginTransaction();
                cmd.Transaction = tx;
                try
                {
                    int count = 0;
                    for (int i = 0; i < SQLStringList.Count; i++)
                    {
                        string strsql = SQLStringList[i];
                        if (strsql.Trim().Length > 1)
                        {
                            cmd.CommandText = strsql;
                            count += cmd.ExecuteNonQuery();
                        }
                    }
                    tx.Commit();
                    result = count;
                }
                catch
                {
                    tx.Rollback();
                    result = -1;
                }
                finally
                {
                    conn.Close();
                }
            }
            return result;
        }
        #endregion

    }
} 
